const pool = require('../models/db');

// 获取商品信息
const getGoods = async (query) => {
  const { goods_id, goods_name, goods_type, remarks } = query;
  let sqlQuery = 'SELECT * FROM goods';
  let conditions = [];
  let values = [];

  if (goods_id) {
    conditions.push('goods_id = $' + (conditions.length + 1));
    values.push(goods_id);
  }
  if (goods_name) {
    conditions.push('goods_name ILIKE $' + (conditions.length + 1));
    values.push(`%${goods_name}%`);
  }
  if (goods_type) {
    conditions.push('goods_type ILIKE $' + (conditions.length + 1));
    values.push(`%${goods_type}%`);
  }
  if (remarks) {
    conditions.push('remarks ILIKE $' + (conditions.length + 1));
    values.push(`%${remarks}%`);
  }

  if (conditions.length > 0) {
    sqlQuery += ' WHERE ' + conditions.join(' AND ');
  }
  const results = await pool.query(sqlQuery, values);
  return { items: results.rows, total: results.rowCount };
};

// 新建商品信息
const addGoods = async (data) => {
  const { goods_name, goods_type, remarks } = data;
  const results = await pool.query(
    'INSERT INTO goods (goods_name, goods_type, remarks) VALUES ($1, $2, $3) RETURNING *',
    [goods_name, goods_type, remarks || null]
  );
  return results.rows[0];
};

// 更新商品信息
const updateGoods = async (id, data) => {
  const { goods_name, goods_type, remarks } = data;
  const results = await pool.query(
    'UPDATE goods SET goods_name = $1, goods_type = $2, remarks = $3 WHERE goods_id = $4 RETURNING *',
    [goods_name, goods_type, remarks || null, id]
  );
  if (results.rowCount === 0) {
    throw new Error('No goods found with this ID');
  }
  return results.rows[0];
};

// 删除商品信息
const deleteGoods = async (id) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('DELETE FROM purchase WHERE goods_id = $1', [id]);
    await client.query('DELETE FROM sales WHERE goods_id = $1', [id]);
    await client.query('DELETE FROM stock WHERE goods_id = $1', [id]);
    const results = await client.query('DELETE FROM goods WHERE goods_id = $1 RETURNING *', [id]);
    if (results.rowCount === 0) {
      throw new Error('No goods found with this ID');
    }
    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
};

// 获取商品信息建议
const getSuggestions = async (query) => {
  const { field, query: searchString } = query;
  const results = await pool.query(
    `SELECT DISTINCT ${field} FROM goods WHERE ${field} ILIKE $1`,
    [`%${searchString}%`]
  );
  return results.rows.map(row => row[field]);
};

module.exports = {
  getGoods,
  addGoods,
  updateGoods,
  deleteGoods,
  getSuggestions,
};
